SET NOCOUNT ON
--------------------------------------------------------------------
if object_id('sp_ColumnInfo', 'P') > 0  drop procedure sp_ColumnInfo
--------------------------------------------------------------------
go

create procedure dbo.sp_ColumnInfo (
	@Table_name sysname,
	@flags      varchar(10) = ''
)as
SET NOCOUNT ON
	-- part of Extreme T-SQL Script http://etsql.codeplex.com/
	-- Bernd Kriszio
	-- https://twitter.com/SQLSulidae  2010-10-28

	DECLARE @flag_c    bit	= case when charindex ('C', @FLAGS) > 0	then 1 else 0 end -- show Count(*) of rows
	
	declare @rowcount bigint
	declare @table_internal int = 0

	if OBJECT_ID('tempdb..#columnInfo') is null
	begin
		create table #columnInfo (
			column_id   int,
			name varchar(30),
			typ  varchar(20),
			max_length int,
			mstype varchar(20),
			oratype varchar(20),
			format varchar(30),
			scale int,
			is_nullable bit,
			is_identity bit,
			used_length int
		)
		SET @table_internal = 1
	end	
		
		insert into #columnInfo 
		select column_id, left (c.name, 30) name, left(t.name, 20) typ , c.max_length
			, left (case when t.name in ('char', 'nchar', 'varchar', 'nvarchar') then t.name 
				+ case when c.max_length = -1 then   '(max)'   else    '('+ ltrim(str(c.max_length)) + ')'    end else   t.name   end, 20) mstype
			, left (case when t.name in ('char', 'nchar', 'varchar', 'nvarchar') 
			             then case t.name when 'char'     then 'CHAR'
			                              when 'nchar'    then 'NCHAR'
			                              when 'varchar'  then 'VARCHAR2'
			                              else                 'NVARCHAR2'
			                              end     + '('+ ltrim(str(c.max_length)) + ')' 
			             else case when t.name in ('bit', 'tinyint', 'smallint', 'int')                        then 'NUMBER(10)'
			                       when t.name in ('bigint')                                                   then 'MUMBER(35)'
								   when t.name in ('datetime')                                                 then 'DATE'
								   when t.name in ('text')                                                     then 'CLOB'
								   when t.name in ('ntext')                                                    then 'NCLOB'
								   when t.name in ('sysname')                                                  then 'VARCHAR2(30)'
								   else                 '*' + t.name + '*'
								   end  end, 20) oratype
			, case when t.name in ('char', 'varchar', 'text'                                         )          then 'dbo.pp_string(/s/, 0, /o/)'
			             when t.name in ('nchar', 'nvarchar', 'ntext', 'sysname')                               then 'dbo.pp_string(/s/, 1, /o/)'
			             when t.name in ('bit', 'tinyint', 'smallint', 'int', 'bigint')                         then 'dbo.pp_int(/s/)'
					     when t.name in ('date', 'smalldatetime', 'datetime', 'datetime2', 'datetimeoffset',
					                     'time' )                                                               then 'dbo.pp_datetime(/s/, /o/)'
					     when t.name in ('timestamp')                                                           then '''null'''           
					     when t.name in ('real', 'float', 'smallmoney', 'money', 'decimal', 'numeric')          then 'dbo.pp_decimal(/s/)'
										                                                                        else ''' + t.name + '''  end format
            , c.scale 
			, c.is_nullable							                                                             
			, is_identity
			, 0
		from sys.columns c
			join sys.types t on c.user_type_id = t.user_type_id  -- user_type_id
		where object_id = OBJECT_ID(@table_name)
		order by column_id

	declare @column_name sysname
	declare @typ varchar(20)
	declare c cursor for
		Select name, typ from #columnInfo
	open c;		
	fetch next from c into @column_name, @typ
	while @@FETCH_STATUS = 0
	begin
		if @typ in  ('varchar', 'text', 'nvarchar', 'ntext')
			Exec( 'Update #columnInfo Set used_length = (Select isnull(max(datalength(isnull(' 
				+ @column_name + ', ''''))), 0) from  ' + @Table_name + ') where name = ''' + @column_name + '''')
		fetch next from c into @column_name, @typ
	end	
	close c;	deallocate c
	
	if @flag_c <> 0
	begin
		declare @cnt bigint
		declare @bigint table (cnt bigint)
		insert into @bigint exec ('select count(1) from ' + @Table_name)
		Select Top 1 @cnt = cnt from @bigint
		Print ltrim(str(@cnt)) + ' rows in ' + @Table_name
		Print ''
	end
		
	if @table_internal <> 0
		select * from #columnInfo 

go


/*

exec sp_ColumnInfo int_vals
exec sp_ColumnInfo num_vals
exec sp_ColumnInfo date_vals
exec sp_ColumnInfo string_vals
exec sp_ColumnInfo other_vals

exec sp_ColumnInfo int_vals, C
select * from int_vals

*/

